/**
 * 
 */
package com.gxa.ehome.dao.impl;




import com.gxa.ehome.dao.BaseDao;
import com.gxa.ehome.dao.CpDao;
import com.gxa.ehome.dao.GetVoDao;
import com.gxa.ehome.entity.Cp;
import com.gxa.ehome.entity.vo.GetVo;
import com.gxa.ehome.util.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
//import com.gxa.userManager.entity.*;

/**
 * UserDaoImpl 作为UserDao的实现类
 * 
 * @author bilei
 * @date 2021年3月8日
 */
    public class GetVoDaoImpl extends BaseDao<GetVo>   implements GetVoDao {
	private DBUtil dbutil = new DBUtil();

//	@Override
//	public List<Cp> selectAll() throws Exception {
//		List<Cp> cpList = super.selectAll(Cp.class);
//		return cpList;
//	}

	@Override
	public List<GetVo> selectByPage(int page, int size) throws Exception {
//		String sql="SELECT get_id AS getvo_id,cp_name AS getvo_cpName,user_phone as getvo_phone," +
//				"cp_colletExpire as getvo_colletExpire,cp_useExpire as getvo_useExpire" +
//				"FROM tb_cp c,tb_get g" +
//				"WHERE c.cp_id = g.cp_id";
		List<GetVo> userList = super.selectByPage(page, size, GetVo.class);
		return userList;


	}

	@Override
	public List<GetVo> searchNameOrPhone(String nameOrphone, Integer page, Integer size) throws Exception {
		List<GetVo> userList = super.selectLikeNameorPhoneByPage(nameOrphone,page, size, GetVo.class);
		return userList;
	}

	@Override
	public List<Cp> selectNotGetCpsByPhone(String phone) throws Exception {
		//查询某手机号用户未领取的优惠券
		//1.手机号 2.申请有效期前 3.未领取的 4.已上架的
		String sql = "SELECT * FROM tb_cp WHERE cp_id \n" +
				"NOT IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
				"AND cp_colletExpire >= DATE_FORMAT(NOW(), '%Y-%m-%d')\n" +
				"AND cp_inUse = '1'";
		PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
		preparedStatement.setObject(1, phone);
		ResultSet resultSet = dbutil.execQuery(preparedStatement);

		List<Cp> list = new ArrayList<Cp>();
		Cp cp = null;
		while (resultSet.next()) {
			cp = new Cp();
			cp.setId(resultSet.getInt(1));
			cp.setName(resultSet.getString(2));
			cp.setDesc(resultSet.getString(3));
			cp.setImg(resultSet.getString(4));
			cp.setRole(resultSet.getString(5));
			cp.setColletExpire(resultSet.getDate(6));
			cp.setUseExpire(resultSet.getDate(7));
			cp.setInUse(resultSet.getBoolean(8));
			list.add(cp);
		}
		dbutil.closeAll();
		return list;
	}

	@Override
	public List<Cp> selectGetCpsByPhone(String phone) throws Exception {
		//查询某手机号用户未领取的优惠券
		//1.手机号 2.使用有效期前 3.已领取的 4.已上架的
		String sql = "SELECT * FROM tb_cp WHERE cp_id\n" +
				"IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
				"AND cp_useExpire >= DATE_FORMAT(NOW(), '%Y-%m-%d')\n" +
				"AND cp_inUse = '1'";
		PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
		preparedStatement.setObject(1, phone);
		ResultSet resultSet = dbutil.execQuery(preparedStatement);

		List<Cp> list = new ArrayList<Cp>();
		Cp cp = null;
		while (resultSet.next()) {
			cp = new Cp();
			cp.setId(resultSet.getInt(1));
			cp.setName(resultSet.getString(2));
			cp.setDesc(resultSet.getString(3));
			cp.setImg(resultSet.getString(4));
			cp.setRole(resultSet.getString(5));
			cp.setColletExpire(resultSet.getDate(6));
			cp.setUseExpire(resultSet.getDate(7));
			cp.setInUse(resultSet.getBoolean(8));
			list.add(cp);
		}
		dbutil.closeAll();
		return list;
	}

	@Override
	public List<Cp> selectOverExpireCpsByPhone(String phone) throws Exception {
		//查询某手机号用户已过期的优惠券
		//1.手机号 2.已领取的 3.超过使用有效期的 4.不论是否上架
		String sql = "SELECT * FROM tb_cp WHERE cp_id\n" +
				"IN (SELECT cp_id FROM tb_get WHERE user_phone = ?)\n" +
				"AND cp_useExpire < DATE_FORMAT(NOW(), '%Y-%m-%d')";
		PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
		preparedStatement.setObject(1, phone);
		ResultSet resultSet = dbutil.execQuery(preparedStatement);

		List<Cp> list = new ArrayList<Cp>();
		Cp cp = null;
		while (resultSet.next()) {
			cp = new Cp();
			cp.setId(resultSet.getInt(1));
			cp.setName(resultSet.getString(2));
			cp.setDesc(resultSet.getString(3));
			cp.setImg(resultSet.getString(4));
			cp.setRole(resultSet.getString(5));
			cp.setColletExpire(resultSet.getDate(6));
			cp.setUseExpire(resultSet.getDate(7));
			cp.setInUse(resultSet.getBoolean(8));
			list.add(cp);
		}
		dbutil.closeAll();
		return list;
	}
	@Override
	public Integer getCpn(Integer cpId, String phone) throws Exception {
		String sql = "INSERT INTO tb_get (cp_id,user_phone)\n" +
				"VALUES (?,?);";
		PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
		preparedStatement.setObject(1, cpId);
		preparedStatement.setObject(2, phone);

		int lines = preparedStatement.executeUpdate();
		dbutil.closeAll();
		return lines;
	}




}
